﻿// Finaquant Analytics - http://finaquant.com/
// Copyright © Finaquant Analytics GmbH
// Email: support@finaquant.com

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using FinaquantCalcs;

// NetOffice: Microsoft Office integration without version limitations
// http://netoffice.codeplex.com/
using Excel = NetOffice.ExcelApi;
using NetOffice.ExcelApi.GlobalHelperModules;
using NetOffice.ExcelApi.Enums;
using XTable = NetOffice.ExcelApi.ListObject;

// Excel DNA: Integrate .NET into Excel
// http://exceldna.codeplex.com/
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using CustomUI = ExcelDna.Integration.CustomUI;

// Calling a non-static method of class ExcelTable in VBA
/*
' Calling ExcelTbl.GetPriceTable in Excel VBA
Sub Test_GetPriceTable()
Dim ExcelTbl As Object: Set ExcelTbl = CreateObject("Finaquant_ExcelTable")

' Call .NET method with parameters
Call ExcelTbl.GetPriceTable("Cost", "Margin1")

' Call .NET method without parameters (macro)
Call ExcelTbl.GetPriceTable_macro

End Sub
*/

namespace FinaquantInExcel
{
    /// <summary>
    /// Class with application oriented table-valued functions
    /// for excel users and VBA programmers. 
    /// Input and Output parameters are generally excel tables (ListObject).
    /// All public and non-static methods in this class are available in Excel VBA.
    /// We will use the class name XTable as synonim for Excel.ListObject
    /// </summary>
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ProgId("Finaquant_ExcelTable")]
    public class ExcelTable
    {
        /// <summary>
        /// Default constructor
        /// </summary>
        public ExcelTable() { }

        // Simple test method without a return value (void) for testing excel integration
        public void AddNumbers(double x, double y)
        {
            double z = x + y;
            // Console.WriteLine("x + y = " + z);
            MessageBox.Show("x + y = " + z.ToString());
        }

        // Simple test method with a return value (double) for testing excel integration
        public double MultiplyNumbers(double x, double y)
        {
            return x * y;
        }

        /// <summary>
        /// Read an excel table (ListObject) into a MatrixTableX
        /// </summary>
        /// <param name="xTbl">Excel table (ListObject)</param>
        /// <param name="mdx">Meta data with field definitions</param>
        /// <param name="TextReplaceNull">Replacement integer value for null in excel table</param>
        /// <param name="NumReplaceNull">Replacement integer value for null in excel table</param>
        /// <param name="KeyFigReplaceNull">Replacement floating value for null in excel table</param>
        /// <returns>Table of type MatrixTableX</returns>
        public MatrixTableX ExcelToMatrixTable(XTable xTbl, MetaDataX mdx,
            string TextReplaceNull = "NULL", int NumReplaceNull = 0, double KeyFigReplaceNull = 0)
        {
            MatrixTableX tbl = new MatrixTableX();
            tbl.ReadFromExcelTable(xTbl, mdx, TextReplaceNull, NumReplaceNull, KeyFigReplaceNull);
            return tbl;
        }

        /// <summary>
        /// Create an excel table (ListObject) by writing MatrixTableX to its range in worksheet.
        /// </summary>
        /// <param name="Tbl">Table of type MatrixTableX</param>
        /// <param name="WSheet">Worksheet object</param>
        /// <param name="TableName">Name of excel table</param>
        /// <param name="CellStr">Upper-left corner of Excel Table in worksheet</param>
        /// <param name="ClearSheetContent">If true, clear whole sheet </param>
        /// <returns>Excel table (ListObject)</returns>
        /// <remarks>
        /// Calling this method from excel VBA requires MS Interop (not NetOffice interop)
        /// because this method has an excel object parameter (worksheet). 
        /// </remarks>
        public XTable MatrixTableToExcel(MatrixTableX Tbl, Excel.Worksheet WSheet, string TableName, 
            string CellStr = "A1", bool ClearSheetContent = false)
        {
            return Tbl.WriteToExcelTable(WSheet, TableName, CellStr, ClearSheetContent);
        }

        /// <summary>
        /// Create test tables in excel sheets
        /// </summary>
        public void CreateTestTables_macro()
        {
            try
            {
                if (MessageBox.Show(
                    "Creating test tables may overwrite existing tables. Is this OK for you?",
                    "Warning!", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {

                    // get active workbook
                    Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();
                    Excel.Workbook wbook = xlapp.ActiveWorkbook;
                    ExcelFunc_NO.CreateTestTables(wbook);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("CreateTestTables: " + ex.Message + "\n");
            }
        }

        /// <summary>
        /// Obtain Price table from Cost and Margin tables.
        /// Excel Tables are passed as ListObject parameters.
        /// Underlying Table Function: MatrixTable.MultiplySelectedKeyFigures()
        /// </summary>
        /// <param name="xCostTbl">ListObject (excel table) for costs</param>
        /// <param name="xMarginTbl">ListObject (excel table) for margins</param>
        /// <param name="xMetaTbl">ListObject (excel table) for field definitions (metadata)</param>
        /// <param name="WSheet">Worksheet object for resultant price table</param>
        /// <param name="PriceTblName">Name of resultant price table (ListObject)</param>
        /// <param name="inKeyFig1"></param>
        /// <param name="inKeyFig1">Selected input key figure from cost table</param>
        /// <param name="inKeyFig2">Selected input key figure from margin table</param>
        /// <param name="outKeyFig">Name of resultant (output) key figure </param>
        /// <param name="StdMargin">Standard margin to be applied on other products not included in margin table</param>
        /// <param name="CellStr">Upper-left corner of the resultant table in excel sheet</param>
        /// <param name="ClearSheetContent">If true, clear the whole sheet before inserting the resultant table</param>
        /// <returns>ListObject (excel table) for resultant price table</returns>
        /// <remarks>
        /// Calling this method from excel VBA requires MS Interop (not NetOffice interop)
        /// because it has an excel object parameter (worksheet). 
        /// </remarks>
        public XTable GetPriceTableX(XTable xCostTbl, XTable xMarginTbl, XTable xMetaTbl,
            Excel.Worksheet WSheet, string PriceTblName, 
            string inKeyFig1 = "costs", string inKeyFig2 = "margin", string outKeyFig = "price",
            double StdMargin = 0.25, string CellStr = "A1", bool ClearSheetContent = true)
        {
            // Typical flow of calculation:

            // Step 1: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // Step 2: Read excel tables (inputs) into MatrixTable objects
            MatrixTable CostTbl = ExcelToMatrixTable(xCostTbl, mdx).matrixTable;
            MatrixTable MarginTbl = ExcelToMatrixTable(xMarginTbl, mdx).matrixTable;

            // Step 3: Generate resultant (output) tables with table functions
            MatrixTable PriceTbl = MatrixTable.MultiplySelectedKeyFigures(
                CostTbl, MarginTbl + 1, inKeyFig1, inKeyFig2, outKeyFig,
                MultiplyRestWith: (StdMargin + 1), JokerMatchesAllvalues: true);


            // Step 4: Write output tables (in this case PriceTbl) into excel tables
            return MatrixTableToExcel(new MatrixTableX(PriceTbl), WSheet, PriceTblName);
        }

        /// <summary>
        /// Obtain Price table by multiplying Cost and Margin tables:
        /// PriceTable = CostTable * (MarginTable + 1).
        /// Underlying Table Function: MatrixTable.MultiplySelectedKeyFigures()
        /// </summary>
        /// <param name="xCostTblName">Name of ListObject (excel table) for costs</param>
        /// <param name="xMarginTblName">Name of ListObject (excel table) for margins</param>
        /// <param name="xMetaTblName">Name of ListObject (excel table) for field definitions.</param>
        /// <param name="WorkbookFullName">Full name of excel workbook (if null, active workbook is assumed)</param>
        /// <param name="xPriceTblName">Name of ListObject (excel table) for the resultant (output) prices</param>
        /// <param name="TargetSheetName">Sheet name for writing resultant table</param>
        /// <param name="inKeyFig1">Selected input key figure from cost table</param>
        /// <param name="inKeyFig2">Selected input key figure from margin table</param>
        /// <param name="outKeyFig">Name of resultant (output) key figure </param>
        /// <param name="StdMargin">Standard margin to be applied on other products not included in margin table</param>
        /// <param name="CellStr">Upper-left corner of the resultant table in excel sheet</param>
        /// <param name="ClearSheetContent">If true, clear the whole sheet before inserting the resultant table</param>
        /// <remarks>
        /// Excel Tables are passed by their names (string).
        /// Example without explicit declaration of metadata (field definitions).
        /// Set xMetaTblName to null if there is no metadata table with field definitions.
        /// </remarks>
        public void GetPriceTable(string xCostTblName, string xMarginTblName, string xMetaTblName = null,
            string WorkbookFullName = null, string xPriceTblName = "Price", string TargetSheetName = "PriceTable",
            string inKeyFig1 = "costs", string inKeyFig2 = "margin", string outKeyFig = "price",
            double StdMargin = 0.25, string CellStr = "A1", bool ClearSheetContent = true)
        {
            // Typical flow of calculation:

            // Step 0: Get current application and active workbook
            Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wbook;

            if (WorkbookFullName == null || WorkbookFullName == String.Empty)
                wbook = xlApp.ActiveWorkbook;
            else
                wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);

            // Step 1: Get ListObjects
            XTable xCostTbl = ExcelFunc_NO.GetListObject(wbook, xCostTblName);
            XTable xMarginTbl = ExcelFunc_NO.GetListObject(wbook, xMarginTblName);

            XTable xMetaTbl = null;
            if (xMetaTblName != null && xMetaTblName != String.Empty) 
                xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            if (xMetaTblName != null && xMetaTblName != String.Empty) 
                mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable CostTbl = ExcelToMatrixTable(xCostTbl, mdx).matrixTable;
            MatrixTable MarginTbl = ExcelToMatrixTable(xMarginTbl, mdx).matrixTable;

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable PriceTbl = MatrixTable.MultiplySelectedKeyFigures(
                CostTbl, MarginTbl + 1, inKeyFig1, inKeyFig2, outKeyFig,
                MultiplyRestWith: (StdMargin + 1), JokerMatchesAllvalues: true);

            // Step 5: Write output tables (in this case PriceTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);
            MatrixTableToExcel(new MatrixTableX(PriceTbl), wsheet, xPriceTblName);
            wsheet.Activate();
        }

        /// <summary>
        /// Obtain price table from cost and margin tables; shows input box for getting parameter values
        /// </summary>
        public void GetPriceTable_macro()
        {
            try
            {
                // get current excel application
                Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

                // inbox parameters
                string title = "Parameter Form for GetPriceTable";

                string prompt = "Please select a single-column range with 3 input values for following parameters:\n\n"
                    + "1) Name of excel table (ListObject) for costs\n"
                    + "2) Name of excel table (ListObject) for margins\n"
                    + "3) Name of excel table (ListObject) for resultant price table\n";

                List<object> ParameterValues;

                // call input box
                if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 3, out ParameterValues))
                {
                    // assign parameter values
                    string xCostTblName = (string)ParameterValues[0];
                    string xMarginTblName = (string)ParameterValues[1];
                    string xPriceTblName = (string)ParameterValues[2];

                    GetPriceTable(xCostTblName, xMarginTblName, 
                        WorkbookFullName: null,
                        xPriceTblName: xPriceTblName
                        );
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("CombineTables: " + ex.Message + "\n");
            }
        }

        /// <summary>
        /// Combine two input tables that have some common attributes (text or numeric).
        /// Underlying Table Function: MatrixTable.CombineTablesFirstMatch()
        /// </summary>
        /// <param name="xTb1Name">Name of 1. excel table (ListObject)</param>
        /// <param name="xTbl2Name">Name of 2. excel table (ListObject)</param>
        /// <param name="xMetaTblName">Name of excel table (ListObject) for field definitions.</param>
        /// <param name="wbook">Excel Workbook object</param>
        /// <param name="xCombinedTblName">Name of resultant combined table</param>
        /// <param name="TargetSheetName">Sheet name for writing resultant table</param>
        /// <param name="JokerMatchesAllvalues">If true, joker value matches all possible attribute values</param>
        /// <param name="TextJoker">Joker (match-all) value for text attributes</param>
        /// <param name="NumJoker">Joker (match-all) value for numeric attributes</param>
        /// <returns>Excel Table (ListObject) object for resultant combined table</returns>
        /// <remarks>
        /// Calling this method from excel VBA requires MS Interop (not NetOffice interop)
        /// because this method has an excel object parameter (workbook). 
        /// </remarks>
        public XTable CombineTablesX(string xTb1Name, string xTbl2Name, string xMetaTblName,
            Excel.Workbook wbook, string xCombinedTblName = "Combined", string TargetSheetName = "CombinedTable", 
            bool JokerMatchesAllvalues = true, string TextJoker = "ALL", int NumJoker = 0)
        {
            // Typical flow of calculation:

            // Step 1: Get ListObjects
            XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xTb1Name);
            XTable xTbl2 = ExcelFunc_NO.GetListObject(wbook, xTbl2Name);
            XTable xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;
            MatrixTable Tbl2 = ExcelToMatrixTable(xTbl2, mdx).matrixTable;

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable CombinedTbl = MatrixTable.CombineTablesFirstMatch(
                Tbl1, Tbl2, JokerMatchesAllvalues, TextJoker, NumJoker);

            // Step 5: Write output tables (in this case CombinedTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);
            return MatrixTableToExcel(new MatrixTableX(CombinedTbl), wsheet, xCombinedTblName);
        }

        /// <summary>
        /// Combine two input tables that have some common attributes (text or numeric).
        /// Underlying Table Function: MatrixTable.CombineTablesFirstMatch()
        /// </summary>
        /// <param name="xTb1Name">Name of 1. excel table (ListObject)</param>
        /// <param name="xTbl2Name">Name of 2. excel table (ListObject)</param>
        /// <param name="xMetaTblName">Name of excel table (ListObject) for field definitions.</param>
        /// <param name="WorkbookFullName">File path of Excel Workbook</param>
        /// <param name="xCombinedTblName">Name of resultant combined table</param>
        /// <param name="TargetSheetName">Sheet name for writing resultant table</param>
        /// <param name="JokerMatchesAllvalues">If true, joker value matches all possible attribute values</param>
        /// <param name="TextJoker">Joker (match-all) value for text attributes</param>
        /// <param name="NumJoker">Joker (match-all) value for numeric attributes</param>
        /// <remarks>
        /// Excel Tables are passed by their names (string); no excel object parameters
        /// </remarks>
        public void CombineTables(string xTb1Name, string xTbl2Name, string xMetaTblName = null,
                string WorkbookFullName = null, string xCombinedTblName = "Combined", string TargetSheetName = "CombinedTable",
                bool JokerMatchesAllvalues = true, string TextJoker = "ALL", int NumJoker = 0)
        {
            // Typical flow of calculation:

            // TEST 
            // MessageBox.Show("Step 0: Get current application and active workbook");

            // Step 0: Get current application and active workbook
            Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wbook;

            if (WorkbookFullName == null || WorkbookFullName == String.Empty)
                wbook = xlApp.ActiveWorkbook;
            else
                wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);

            // TEST 
            // MessageBox.Show("Step 1: Get ListObjects, workook: " + wbook.FullName);

            // Step 1: Get ListObjects
            XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xTb1Name);
            XTable xTbl2 = ExcelFunc_NO.GetListObject(wbook, xTbl2Name);

            XTable xMetaTbl = null;
            if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // TEST 
            // MessageBox.Show("Step 2: Get meta data with field definitions");

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // TEST 
            // MessageBox.Show("Step 3: Read excel tables (inputs) into MatrixTable objects");

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;
            MatrixTable Tbl2 = ExcelToMatrixTable(xTbl2, mdx).matrixTable;

            // TEST 
            // MessageBox.Show("Step 4: Generate resultant (output) tables with table functions");

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable CombinedTbl = MatrixTable.CombineTablesFirstMatch(
                Tbl1, Tbl2, JokerMatchesAllvalues, TextJoker, NumJoker);

            //TEST
            // CombinedTbl.ViewTable("Resultant Combined Table");

            // Step 5: Write output tables (in this case CombinedTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);

            MatrixTableToExcel(new MatrixTableX(CombinedTbl), wsheet, xCombinedTblName);
            wsheet.Activate();
        }

        /// <summary>
        /// Combine two input tables that have some common attributes (text or numeric).
        /// Shows input box for getting parameters.
        /// Underlying Table Function: MatrixTable.CombineTables()
        /// </summary>
        public void CombineTables_macro()
        {
            try
            {
                // get current excel application
                Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

                // inbox parameters
                string title = "Parameter Form for CombineTables";

                string prompt = "Please select a single-column range with 2 input values for following parameters:\n\n"
                    + "1) Name of 1. excel table (ListObject)\n"
                    + "2) Name of 2. excel table (ListObject)\n";

                List<object> ParameterValues;

                // call input box
                if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 2, out ParameterValues))
                {
                    // assign parameter values
                    string xTb1Name = (string)ParameterValues[0];
                    string xTbl2Name = (string)ParameterValues[1];

                    CombineTables(xTb1Name, xTbl2Name);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("CombineTables: " + ex.Message + "\n");
            }
        }

        /// <summary>
        /// Add two input tables (table addition) with selected key figures.
        /// Underlying table function: MatrixTable.AddSelectedKeyFigures()
        /// </summary>
        /// <param name="xTbl1Name">Name of 1. input table (ListObject)</param>
        /// <param name="xTbl2Name">Name of 2. input table (ListObject)</param>
        /// <param name="InputKeyfig1">Selected key figure from 1. input table</param>
        /// <param name="InputKeyfig2">Selected key figure from 2. input table</param>
        /// <param name="OutputKeyfig">Output (resultant) key figure</param>
        /// <param name="xMetaTblName">Name of excel table (ListObject) with field definitions</param>
        /// <param name="WorkbookFullName">File path for workbook</param>
        /// <param name="xResultantTblName">Name of resultant output table (ListObject)</param>
        /// <param name="TargetSheetName">Target sheet for resultant table</param>
        public void AddTables(string xTbl1Name, string xTbl2Name,
            string InputKeyfig1, string InputKeyfig2, string OutputKeyfig,
             string xMetaTblName = null, string WorkbookFullName = null,
            string xResultantTblName = "Result", string TargetSheetName = "ResultTable")
        {

            // Typical flow of calculation:

            // Step 0: Get current application and active workbook
            Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wbook;

            if (WorkbookFullName == null || WorkbookFullName == String.Empty)
                wbook = xlApp.ActiveWorkbook;
            else
                wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);

            // Step 1: Get ListObjects
            XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xTbl1Name);
            XTable xTbl2 = ExcelFunc_NO.GetListObject(wbook, xTbl2Name);

            XTable xMetaTbl = null;
            if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;
            MatrixTable Tbl2 = ExcelToMatrixTable(xTbl2, mdx).matrixTable;

            // TEST
            // Tbl1.ViewTable(xTbl1Name);
            // Tbl2.ViewTable(xTbl2Name);

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable ResultantTbl = MatrixTable.AddSelectedKeyFigures(
                Tbl1, Tbl2, InputKeyfig1, InputKeyfig2, OutputKeyfig,
                AddToRest: 0.0, JokerMatchesAllvalues: true,
                TextJoker: "ALL", NumJoker: 0);

            // TEST
            // ResultantTbl.ViewTable(xResultantTblName);

            // Step 5: Write output tables (in this case CombinedTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);

            MatrixTableToExcel(new MatrixTableX(ResultantTbl), wsheet, xResultantTblName);
            wsheet.Activate();
        }

        /// <summary>
        /// Add two input tables (table addition) with selected key figures.
        /// Calls input range box for getting parameter values.
        /// Underlying table function: MatrixTable.AddSelectedKeyFigures()
        /// </summary>
        public void AddTables_macro()
        {
            try
            {
                // get current excel application
                Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

                // inbox parameters
                string title = "Parameter Form for AddTables";

                string prompt = "Please select a single-column range with 5 input values for following parameters:\n\n"
                    + "1) Name of 1. input table\n"
                    + "2) Name of 2. input table\n"
                    + "3) Selected key figure from 1. input table\n"
                    + "4) Selected key figure from 2. input table\n"
                    + "5) Resultant (output) key figure\n";

                List<object> ParameterValues;

                // call input box
                if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 5, out ParameterValues))
                {
                    // assign parameter values
                    string xTb1Name = (string)ParameterValues[0];
                    string xTbl2Name = (string)ParameterValues[1];

                    string InputKeyfig1 = (string)ParameterValues[2];
                    string InputKeyfig2 = (string)ParameterValues[3];
                    string OutputKeyfig = (string)ParameterValues[4];

                    AddTables(xTb1Name, xTbl2Name,
                        InputKeyfig1, InputKeyfig2, OutputKeyfig);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("AddTables: " + ex.Message + "\n");
            }
        }

        /// <summary>
        /// Multiply two input tables (table multiplication) with selected key figures.
        /// Underlying table function: MatrixTable.MultiplySelectedKeyFigures()
        /// </summary>
        /// <param name="xTbl1Name">Name of 1. input table (ListObject)</param>
        /// <param name="xTbl2Name">Name of 2. input table (ListObject)</param>
        /// <param name="InputKeyfig1">Selected key figure from 1. input table</param>
        /// <param name="InputKeyfig2">Selected key figure from 2. input table</param>
        /// <param name="OutputKeyfig">Output (resultant) key figure</param>
        /// <param name="xMetaTblName">Name of excel table (ListObject) with field definitions</param>
        /// <param name="WorkbookFullName">File path for workbook</param>
        /// <param name="xResultantTblName">Name of resultant output table (ListObject)</param>
        /// <param name="TargetSheetName">Target sheet for resultant table</param>
        public void MultiplyTables(string xTbl1Name, string xTbl2Name,
            string InputKeyfig1, string InputKeyfig2, string OutputKeyfig,
             string xMetaTblName = null, string WorkbookFullName = null,
            string xResultantTblName = "Result", string TargetSheetName = "ResultTable")
        {

            // Typical flow of calculation:

            // Step 0: Get current application and active workbook
            Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wbook;

            if (WorkbookFullName == null || WorkbookFullName == String.Empty)
                wbook = xlApp.ActiveWorkbook;
            else
                wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);

            // Step 1: Get ListObjects
            XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xTbl1Name);
            XTable xTbl2 = ExcelFunc_NO.GetListObject(wbook, xTbl2Name);

            XTable xMetaTbl = null;
            if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;
            MatrixTable Tbl2 = ExcelToMatrixTable(xTbl2, mdx).matrixTable;

            // TEST
            // Tbl1.ViewTable(xTbl1Name);
            // Tbl2.ViewTable(xTbl2Name);

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable ResultantTbl = MatrixTable.MultiplySelectedKeyFigures(
                Tbl1, Tbl2, InputKeyfig1, InputKeyfig2, OutputKeyfig,
                MultiplyRestWith: 1.0, JokerMatchesAllvalues: true,
                TextJoker: "ALL", NumJoker: 0);

            // TEST
            // ResultantTbl.ViewTable(xResultantTblName);

            // Step 5: Write output tables (in this case CombinedTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);

            MatrixTableToExcel(new MatrixTableX(ResultantTbl), wsheet, xResultantTblName);
            wsheet.Activate();
        }

        /// <summary>
        /// Multiply two input tables (table multiplication) with selected key figures.
        /// Calls input range box for getting parameter values.
        /// Underlying table function: MatrixTable.MultiplySelectedKeyFigures()
        /// </summary>
        public void MultiplyTables_macro()
        {
            try
            {
                // get current excel application
                Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

                // inbox parameters
                string title = "Parameter Form for MultiplyTables";

                string prompt = "Please select a single-column range with 5 input values for following parameters:\n\n"
                    + "1) Name of 1. input table\n"
                    + "2) Name of 2. input table\n"
                    + "3) Selected key figure from 1. input table\n"
                    + "4) Selected key figure from 2. input table\n"
                    + "5) Resultant (output) key figure\n";

                List<object> ParameterValues;

                // call input box
                if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 5, out ParameterValues))
                {
                    // assign parameter values
                    string xTb1Name = (string)ParameterValues[0];
                    string xTbl2Name = (string)ParameterValues[1];

                    string InputKeyfig1 = (string)ParameterValues[2];
                    string InputKeyfig2 = (string)ParameterValues[3];
                    string OutputKeyfig = (string)ParameterValues[4];

                    MultiplyTables(xTb1Name, xTbl2Name,
                        InputKeyfig1, InputKeyfig2, OutputKeyfig);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("MultiplyTables: " + ex.Message + "\n");
            }
        }

        /// <summary>
        /// Simple pro-rate distribution; returns table with distributed amounts.
        /// The source amount in SourceTable is disrubuted to target amounts w.r.t. keys (or ratios) given in KeyTable.
        /// Important: Both Source and Key tables must have exactly one key figure.
        /// </summary>
        /// <param name="xSourceTblName">Name of source table with source amounts to be distributed</param>
        /// <param name="xDistrKeyTblName">Name if key table with key amounts (or ratios) used for the distribution</param>
        /// <param name="ResultantKeyFig">Name of key figure with the resultant distributed amounts</param>
        /// <param name="KeySumKeyFig">Name of the key figure with the sum of key amounts</param>
        /// <param name="xMetaTblName">Name of metadata table with field definitions</param>
        /// <param name="WorkbookFullName">File path of workbook</param>
        /// <param name="xResultantTblName">Name of resultant combined table with distributed amounts</param>
        /// <param name="TargetSheetName">Target sheet for resultant table</param>
        public void DistributeTable(string xSourceTblName, string xDistrKeyTblName,
            string ResultantKeyFig, string KeySumKeyFig,
            string xMetaTblName = null, string WorkbookFullName = null,
            string xResultantTblName = "Distrib", string TargetSheetName = "DistribTable")
        {
            // Typical flow of calculation:

            // Step 0: Get current application and active workbook
            Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wbook;

            if (WorkbookFullName == null || WorkbookFullName == String.Empty)
                wbook = xlApp.ActiveWorkbook;
            else
                wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);

            // Step 1: Get ListObjects
            XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xSourceTblName);
            XTable xTbl2 = ExcelFunc_NO.GetListObject(wbook, xDistrKeyTblName);

            XTable xMetaTbl = null;
            if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // TEST
            // MessageBox.Show("Step 3: Read excel tables (inputs) into MatrixTable objects");

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;
            MatrixTable Tbl2 = ExcelToMatrixTable(xTbl2, mdx).matrixTable;

            // TEST
            // MessageBox.Show("Step 4: Generate resultant (output) tables with table functions");

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable ResultantTbl = AppUtility.SimpleDistribution(Tbl1, Tbl2,
                ResultantKeyFig, KeySumKeyFig);

            // TEST
            // MessageBox.Show("Step 5: Write output tables (in this case ResultantTbl) into excel tables");
            // ResultantTbl.ViewTable(xResultantTblName);

            // Step 5: Write output tables (in this case ResultantTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);

            MatrixTableToExcel(new MatrixTableX(ResultantTbl), wsheet, xResultantTblName);
            wsheet.Activate();
        }

        /// <summary>
        /// Simple pro-rate distribution; returns table with distributed amounts.
        /// </summary>
        public void DistributeTable_macro()
        {
            try
            {
                // get current excel application
                Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

                // inbox parameters
                string title = "Parameter Form for DistributeTable";

                string prompt = "Please select a single-column range with 4 input values for following parameters:\n\n"
                    + "1) Name of source table\n"
                    + "2) Name of key table with distribution ratios\n"
                    + "3) Name of resultant key figure\n"
                    + "4) Name of key figure for sum of key values\n";

                List<object> ParameterValues;

                // call input box
                if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 4, out ParameterValues))
                {
                    // assign parameter values
                    string xTb1Name = (string)ParameterValues[0];
                    string xTbl2Name = (string)ParameterValues[1];

                    string ResultantKeyFig = (string)ParameterValues[2];
                    string KeySumKeyFig = (string)ParameterValues[3];

                    DistributeTable(xTb1Name, xTbl2Name,
                        ResultantKeyFig, KeySumKeyFig);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("DistributeTable: " + ex.Message + "\n");
            }
        }

        /// <summary>
        /// Create a new table with all possible combinations of given field values in input table.
        /// If a value set is not explicity given for a field, single default value is assumed as value set.
        /// </summary>
        /// <param name="xTblName">Name of excel table (ListObject) with field values</param>
        /// <param name="xMetaTblName">Name of metadata table with field definitions</param>
        /// <param name="WorkbookFullName">File path of workbook</param>
        /// <param name="DefaultTextVal">Default text attribute value</param>
        /// <param name="DefaultNumVal">Default numeric attribute value</param>
        /// <param name="DefaultKeyFigVal">Default key figure value</param>
        /// <param name="xResultantTblName">Name of resultant excel table (ListObject)</param>
        /// <param name="TargetSheetName">Target sheet for inserting resultant table</param>
        public void CombinateFieldValues(string xTblName, 
            string xMetaTblName = null, string WorkbookFullName = null, 
            string DefaultTextVal = "", int DefaultNumVal = 0, double DefaultKeyFigVal = 0,
            string xResultantTblName = "FieldComb", string TargetSheetName = "FieldCombTable")
        {
            // Typical flow of calculation:

            // Step 0: Get current application and active workbook
            Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wbook;

            if (WorkbookFullName == null || WorkbookFullName == String.Empty)
                wbook = xlApp.ActiveWorkbook;
            else
                wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);

            // Step 1: Get ListObjects
            XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xTblName);

            XTable xMetaTbl = null;
            if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;

            // TEST
            // Tbl1.ViewTable("Table with field values; now get combination table");

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable ResultantTbl = MatrixTable.CombinateFieldValues_A(Tbl1,
                DefaultTextVal, DefaultNumVal, DefaultKeyFigVal);

            // TEST
            // ResultantTbl.ViewTable("Combination table; now write it into excel");

            // Step 5: Write output tables (in this case ResultantTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);
            MatrixTableToExcel(new MatrixTableX(ResultantTbl), wsheet, xResultantTblName);
            wsheet.Activate();
        }

        /// <summary>
        /// Create a new table with all possible combinations of given field values in input table.
        /// </summary>
        public void CombinateFieldValues_macro()
        {
            try
            {
                // get current excel application
                Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

                // inbox parameters
                string title = "Parameter Form for CombinateFieldValues";

                string prompt = "Please select a single-column range with 1 input values for following parameters:\n\n"
                    + "1) Name of input table with field values\n";

                List<object> ParameterValues;

                // call input box
                if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 1, out ParameterValues))
                {
                    // assign parameter values
                    string xTb1Name = (string)ParameterValues[0];

                    CombinateFieldValues(xTb1Name);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("CombinateFieldValues: " + ex.Message + "\n");
            }
        }

        /// <summary>
        /// Filter input table BaseTbl with a condition table CondTbl.
        /// if ExcludeMatchedRows = false (default case) output table includes only rows that match with
        /// some row(s) of condition table CondTbl.
        /// </summary>
        /// <param name="xBaseTblName">Input base table to be filtered</param>
        /// <param name="xCondTblName">Condition table defining filtering criteria</param>
        /// <param name="ExcludeMatchedRows">If true, exclude matched rows from input table</param>
        /// <param name="JokerMatchesAllvalues">If true, JokerValue matches all other values</param>
        /// <param name="TextJoker">Joker (match-all) value for text attributes</param>
        /// <param name="NumJoker">Joker (match-all) value for numeric attributes</param>
        /// <param name="xMetaTblName">Name of metadata table with field definitions</param>
        /// <param name="WorkbookFullName">File path of workbook</param>
        /// <param name="xResultantTblName">Name of resultant excel table (ListObject)</param>
        /// <param name="TargetSheetName">Target sheet for inserting resultant table</param>
        public void FilterTable(string xBaseTblName, string xCondTblName,
            bool ExcludeMatchedRows = false, bool JokerMatchesAllvalues = true,
            string TextJoker = "ALL", int NumJoker = 0,
            string xMetaTblName = null, string WorkbookFullName = null,
            string xResultantTblName = "Filtered", string TargetSheetName = "FilteredTable")
        {
            // Typical flow of calculation:

            // Step 0: Get current application and active workbook
            Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wbook;

            if (WorkbookFullName == null || WorkbookFullName == String.Empty)
                wbook = xlApp.ActiveWorkbook;
            else
                wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);

            // Step 1: Get ListObjects
            XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xBaseTblName);
            XTable xTbl2 = ExcelFunc_NO.GetListObject(wbook, xCondTblName);

            XTable xMetaTbl = null;
            if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);

            // Step 2: Get meta data with field definitions
            MetaDataX mdx = new MetaDataX();
            if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
            MetaData md = mdx.metaData;

            // Step 3: Read excel tables (inputs) into MatrixTable objects
            MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;
            MatrixTable Tbl2 = ExcelToMatrixTable(xTbl2, mdx).matrixTable;

            // Step 4: Generate resultant (output) tables with table functions
            MatrixTable ResultantTbl = MatrixTable.FilterTableA(Tbl1, Tbl2,
                ExcludeMatchedRows, JokerMatchesAllvalues, TextJoker, NumJoker);

            // Step 5: Write output tables (in this case ResultantTbl) into excel tables
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);

            MatrixTableToExcel(new MatrixTableX(ResultantTbl), wsheet, xResultantTblName);
            wsheet.Activate();
        }

        /// <summary>
        /// Filter input table BaseTbl with a condition table CondTbl.
        /// if ExcludeMatchedRows = false (default case) output table includes only rows that match with
        /// some row(s) of condition table CondTbl.
        /// </summary>
        public void FilterTable_macro()
        {
            try
            {
                // get current excel application
                Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

                // inbox parameters
                string title = "Parameter Form for FilterTable";

                string prompt = "Please select a single-column range with 2 input values for following parameters:\n\n"
                    + "1) Name of base table to be filtered\n"
                    + "2) Name of condition table defining filtering criteria\n";

                List<object> ParameterValues;

                // call input box
                if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 2, out ParameterValues))
                {
                    // assign parameter values
                    string xTb1Name = (string)ParameterValues[0];
                    string xTb2Name = (string)ParameterValues[1];

                    FilterTable(xTb1Name, xTb2Name);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("FilterTable: " + ex.Message + "\n");
            }
        }
    }

#if ExcelDNA

    // requires "using ExcelDna.ComInterop"
    [ComVisible(false)]
    class ExcelAddin : IExcelAddIn
    {
        public void AutoOpen()
        {
            ComServer.DllRegisterServer();
        }
        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
        }
    }

    [ComVisible(true)]
    public class Ribbon : CustomUI.ExcelRibbon
    {
        public void RunControlID(CustomUI.IRibbonControl ctl)
        {
            ((Excel.Application)ExcelDnaUtil.Application).Run(ctl.Id);
        }

        public void RunControlIDWithTag(CustomUI.IRibbonControl ctl)
        {
            ((Excel.Application)ExcelDnaUtil.Application).Run(ctl.Id, ctl.Tag);
        }
    }

#endif  // ExcelDNA
}
